package com.siemens.industrial.web.mvc.dao.admin;

import com.siemens.industrial.model.admin.Device;
import com.siemens.industrial.model.admin.GatewayState;
import com.siemens.industrial.model.admin.GatewayInfo;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/9/18.
 */
@Service
public class DeviceDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String getDeviceListQuery = "SELECT * FROM `siemens`.`device`";
    private static final String getOneByIdQuery = "SELECT * FROM `siemens`.`device` WHERE `device_id`=?";
    private static final String getOneByUniqueIdQuery = "SELECT * FROM `siemens`.`device` WHERE `device_unique_id` = ?";
    private static final String getCountByTenantIdQuery = "SELECT COUNT(*) count from `siemens`.`device` where `tenant_id`=?;";
    // please verify these sql queries
    private static final String addDeviceQuery = "INSERT INTO `siemens`.`device` " +
            "(`device_id`, `device_unique_id`, `tenant_id`, `device_model`, `device_type`, `group`, `line`, `purchase_date`, `device_name`, `device_manufactory`, `description`, `device_img`) " +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    private static final String updateDevice = "UPDATE `siemens`.`device` SET `device_model`=?, `device_type`=?, `group`=?, `line`=?, `purchase_date`=?, `device_name`=?, `device_manufactory`=?, `description`=?, `device_img`=? WHERE `device_id`=? and`tenant_id`=?;";
    private static final String updateDeviceQuery = "UPDATE `siemens`.`device` SET `delete_time`=? WHERE `device_id`=?";
    private static final String getDeviceListQueryOnNotDelete = "SELECT * FROM `siemens`.`device` WHERE delete_time IS NULL";
    private static final String saveGateweyInfo = "INSERT INTO `siemens`.`gateway_info` (`version`, `clientId`, `hardware`, `software`) VALUES (?,?,?,?);";
    private static final String saveGatewayState = "INSERT INTO `siemens`.`gateway_state` (`version`, `clientId`, `time`, `power`) VALUES (?, ?, ?, ?);";
    private static final String getCurrentStatusForGateway = "SELECT version,clientId,max(time) time FROM siemens.gateway_state group by version,clientId;";

    private static final String getTenantNameQuery = "SELECT `tenant_id`,`tenant_name`,`tenant_key` FROM `siemens`.`tenant` order by `tenant_name`;";
    private static final String getDeviceTypesQuery = "select distinct `device_type` from  siemens.device where delete_time is null order by `device_type`;";
    private static final String getDeviceModelsQuery = "select distinct `device_model` from  siemens.device where delete_time is null order by `device_model`;";
    private static final String getDeviceLinesQuery = "select distinct `line` from  siemens.device where delete_time is null order by `line`;";
    private static final String getDeviceGroupsQuery = "select distinct `group` from  siemens.device where delete_time is null order by `group`;";

    private static final String getDeviceGroupsByTenantIDQuery = "select distinct `group` from  siemens.device where delete_time is null and `tenant_id`=? order by `group` desc;";
    private static final String getDeviceLinesByTenantIDQuery = "select distinct `line` from  siemens.device where delete_time is null and `tenant_id`=? order by `line` desc;";
    private static final String getDeviceTypesByTenantIDQuery = "select distinct `device_type` from  siemens.device where delete_time is null and `tenant_id`=? order by `device_type` desc;";
    private static final String getDeviceModelsByTenantIDQuery = "select distinct `device_model` from  siemens.device where delete_time is null and `tenant_id`=? order by `device_model` desc;";
    private static final String getDeviceMTIdByTenantIDQuery = "select distinct `device_unique_id` from  siemens.device where delete_time is null and `tenant_id`=? order by `device_unique_id` desc;";

    private static final String getDeviceMTIdByModelAndTypeQuery = "select b.* from (select distinct tenant_id from `siemens`.`device` where `device_model`= ? and `device_type`= ?) a inner join `siemens`.`tenant` b on a.tenant_id = b.tenant_id order by b.tenant_name;";
    private static final String getDeviceGroupsByModelAndTypeQuery = "select distinct `group` from `siemens`.`device` where `device_model`= ? and `device_type`= ? order by `group` asc;";
    private static final String getDeviceLinesByModelAndTypeQuery = "select distinct `line` from `siemens`.`device` where `device_model`= ? and `device_type`= ? order by `line` asc;";

    private static final String findAllDeviceOrderByMTIdQuery = "select * from `siemens`.`device` where `delete_time is null;`";

    public List<Device> getDevices() {
        return jdbcTemplate.query(getDeviceListQuery, Device.getRowMapper());
    }

    public Device findOneById(String deviceId) {
        try {
            return jdbcTemplate.queryForObject(getOneByIdQuery, new Object[]{deviceId}, Device.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public Device findOneByUniqueId(String deviceUniqueId) {
        try {
            return jdbcTemplate.queryForObject(getOneByUniqueIdQuery, new Object[]{deviceUniqueId}, Device.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public int updateById(String deviceId) {
        return jdbcTemplate.update(updateDeviceQuery, new Date(), deviceId);
    }

    public void save(Device device) {
        jdbcTemplate.update(addDeviceQuery, pss -> {
            pss.setString(1, device.getDeviceId());
            pss.setString(2, device.getDeviceUniqueId());
            pss.setString(3, device.getTenantId());
            pss.setString(4, device.getModel());
            pss.setString(5, device.getType());
            pss.setString(6, device.getGroup());
            pss.setString(7, device.getLine());
            pss.setString(8, device.getPurchaseDate());
            pss.setString(9, device.getDeviceName());
            pss.setString(10, device.getManufacturer());
            pss.setString(11, device.getDescription());
            pss.setString(12, device.getDeviceImg());
        });
    }

    public void update(Device device) {
        jdbcTemplate.update(updateDevice, pss -> {
            pss.setString(1, device.getModel());
            pss.setString(2, device.getType());
            pss.setString(3, device.getGroup());
            pss.setString(4, device.getLine());
            pss.setString(5, device.getPurchaseDate());
            pss.setString(6, device.getDeviceName());
            pss.setString(7, device.getManufacturer());
            pss.setString(8, device.getDescription());
            pss.setString(9, device.getDeviceImg());
            pss.setString(10, device.getDeviceId());
            pss.setString(11, device.getDeviceUniqueId());
        });
    }

    public int getDeviceCountByTenantId(String tenantId) {
        return jdbcTemplate.queryForObject(getCountByTenantIdQuery, new Object[]{tenantId}, Integer.class);
    }

    public List<Device> getDeviceList() {
        return jdbcTemplate.query(getDeviceListQueryOnNotDelete, Device.getRowMapper());
    }

    @Transactional
    public void saveGateweyInfo(GatewayInfo gatewayInfo) {
        jdbcTemplate.update(saveGateweyInfo, pss -> {
            pss.setString(1, gatewayInfo.getVersion());
            pss.setString(2, gatewayInfo.getClientId());
            pss.setString(3, gatewayInfo.getHardware());
            pss.setString(4, gatewayInfo.getSoftware());
        });
    }

    @Transactional
    public GatewayInfo getOneGateweyInfoByClientid(String clientId) {
        try {
            return jdbcTemplate.queryForObject(getOneByIdQuery, new Object[]{clientId}, GatewayInfo.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Transactional
    public void saveGateweyState(GatewayState gatewayState) {
        jdbcTemplate.update(saveGatewayState, pss -> {
            pss.setInt(1, gatewayState.getVersion());
            pss.setString(2, gatewayState.getClientId());
            pss.setTimestamp(3, new java.sql.Timestamp(gatewayState.getTime().getTime()));
            pss.setInt(4, gatewayState.getPower());
        });
    }

    public List<Map<String, Object>> getGateWayState() {
        return jdbcTemplate.queryForList(getCurrentStatusForGateway);
    }

    public List<Map<String, Object>> getTenantNames() {
        return jdbcTemplate.queryForList(getTenantNameQuery);
    }

    public List<Map<String, Object>> getLines() {
        return jdbcTemplate.queryForList(getDeviceLinesQuery);
    }

    public List<Map<String, Object>> getGroups() {
        return jdbcTemplate.queryForList(getDeviceGroupsQuery);
    }

    public List<Map<String, Object>> getTypes() {
        return jdbcTemplate.queryForList(getDeviceTypesQuery);
    }

    public List<Map<String, Object>> getModels() {
        return jdbcTemplate.queryForList(getDeviceModelsQuery);
    }

    public List<Map<String, Object>> getlinesByTenantId(String tenantId) {
        return jdbcTemplate.queryForList(getDeviceLinesByTenantIDQuery, tenantId);
    }

    public List<Map<String, Object>> getGroupsByTenantId(String tenantId) {
        return jdbcTemplate.queryForList(getDeviceGroupsByTenantIDQuery, tenantId);
    }

    public List<Map<String, Object>> getTypesByTenantId(String tenantId) {
        return jdbcTemplate.queryForList(getDeviceTypesByTenantIDQuery, tenantId);
    }

    public List<Map<String, Object>> getModelsByTenantId(String tenantId) {
        return jdbcTemplate.queryForList(getDeviceModelsByTenantIDQuery, tenantId);
    }

    public List<Map<String, Object>> getMTIdByTenantId(String tenantId) {
        return jdbcTemplate.queryForList(getDeviceMTIdByTenantIDQuery, tenantId);
    }

    public List<Map<String, Object>> searchBySql(String sql) {
        return jdbcTemplate.queryForList(sql);
    }

    public List<Map<String,Object>> getDeviceMTIdByModelAndType(String model,String type){
        return jdbcTemplate.queryForList(getDeviceMTIdByModelAndTypeQuery,model,type);
    }
    public List<Map<String,Object>> getDeviceGroupsByModelAndType(String model,String type){
        return jdbcTemplate.queryForList(getDeviceGroupsByModelAndTypeQuery,model,type);
    }
    public List<Map<String,Object>> getDeviceLinesByModelAndType(String model,String type){
        return jdbcTemplate.queryForList(getDeviceLinesByModelAndTypeQuery,model,type);
    }

    public List<Device> getDeviceListOrderByMTId() {
        return jdbcTemplate.query(findAllDeviceOrderByMTIdQuery, Device.getRowMapper());
    }
}
